package com.isoftstone.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


/**
 * 从EXCEL导入到数据库
 * 创建时间：2014年12月23日
 * @version
 */
public class ObjectExcelRead {

	/**
	 * @param filepath //文件路径
	 * @param filename //文件名
	 * @param startrow //开始行号
	 * @param startcol //开始列号
	 * @param sheetnum //sheet
	 * @return list
	 */

	@Deprecated
	public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Object> varList = new ArrayList<Object>();

		try {
			File target = new File(filepath, filename);
			FileInputStream fi = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fi);
			HSSFSheet sheet = wb.getSheetAt(sheetnum); 					//sheet 从0开始
			int rowNum = sheet.getLastRowNum() + 1; 					//取得最后一行的行号

			for (int i = startrow; i < rowNum; i++) {					//行循环开始
				
				PageData varpd = new PageData();
				HSSFRow row = sheet.getRow(i); 							//行
				int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置

				for (int j = startcol; j < cellNum; j++) {				//列循环开始
					
					HSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						switch (cell.getCellType()) { 					// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
						case 0:
							cellValue = String.valueOf((int) cell.getNumericCellValue());
							break;
						case 1:
							cellValue = cell.getStringCellValue();
							break;
						case 2:
							cellValue = cell.getNumericCellValue() + "";
							// cellValue = String.valueOf(cell.getDateCellValue());
							break;
						case 3:
							cellValue = "";
							break;
						case 4:
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case 5:
							cellValue = String.valueOf(cell.getErrorCellValue());
							break;
						}
					} else {
						cellValue = "";
					}
					
					varpd.put("var"+j, cellValue);
					
				}
				varList.add(varpd);
			}

		} catch (Exception e) {
			System.out.println(e);
		}
		
		return varList;
	}

   /**
    * 作者:wangjs
    * 日期:2016-06-25 10:27:39 
    * 功能: 对去xls 和  xlsx 文件
    * @param
    * @return 
    */
    public static List<Object> readExcel(String filepath, String filename, int startrow,int sheetnum){
        List<Object> varList = new ArrayList<Object>();
        DecimalFormat df = new DecimalFormat("#.##");//修改excel表可以上传小数
        try {

            String path = filepath + File.separator + filename;

            File file = new File(path);

            InputStream inputStream = new FileInputStream(file);
            String fileName = file.getName();
            Workbook wb = null;
            if(fileName.endsWith("xls")){
                wb = new HSSFWorkbook(inputStream);//解析xls格式
            }else if(fileName.endsWith("xlsx")){
                wb = new XSSFWorkbook(inputStream);//解析xlsx格式
            }else{
                throw new RuntimeException("文件格式不正确!");
            }
            Sheet sheet = wb.getSheetAt(sheetnum);//第一个工作表

            int firstRowIndex = sheet.getFirstRowNum() + startrow - 1 ; // 从第二行开始

            int lastRowIndex = sheet.getLastRowNum();

            for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex ++){
                //-----------------
                PageData varpd = new PageData();
                Row row = sheet.getRow(rIndex);
                if(row != null){
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    for(int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex ++){
                        Cell cell = row.getCell(cIndex);
                        String cellValue = "";
                        if(cell != null){
                           // value = cell.toString();
                           //System.out.print(value+"\t");
                            if (null != cell) {
                                switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                                    case HSSFCell.CELL_TYPE_NUMERIC:
                                        cellValue = String.valueOf((int) cell.getNumericCellValue());
                                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                        if(HSSFDateUtil.isCellDateFormatted(cell)){
                                            cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                                            break;
                                        }
                                        cellValue = df.format(cell.getNumericCellValue());
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING:
                                        cellValue = cell.getStringCellValue();
                                        break;
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        cellValue = String.valueOf(cell.getNumericCellValue());//处理xlsx中公式
                                        break;
                                    case HSSFCell.CELL_TYPE_BLANK:
                                        cellValue = "";
                                        break;
                                    case HSSFCell.CELL_TYPE_BOOLEAN:
                                        cellValue = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    case HSSFCell.CELL_TYPE_ERROR:
                                        cellValue = String.valueOf(cell.getErrorCellValue());
                                        break;

                                }
                            } else {
                                cellValue = "";
                            }
                        }

                        varpd.put("col"+cIndex, cellValue);
                    }

                    varList.add(varpd);
                }
            }
        } catch (FileNotFoundException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        } catch (IOException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        }
        return varList;
    }
}
